package com.witsoft.device.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.witsoft.device.entity.DeviceReporterMonth;
import com.witsoft.device.model.RunAndOpenTimesAllMonth;
import com.witsoft.device.model.RunningTimeAllMonth;
import com.witsoft.device.model.RunningTimesEachMonth;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.List;


@Repository
public interface DeviceReporterDao extends BaseMapper<DeviceReporterMonth> {

    /**
     * @desc 获取当月所有的设备开机时间
     * @return
     */
    @Select("select COALESCE(sum(total_spent),0) as seconds from device_status_timeline where to_char(event_time,'yyyy-MM') = to_char(NOW(),'yyyy-MM') and status = '3'")
    Long getAllDeviceSumOpeningMonth();


    /**
     * @desc 获取当月所有设备的运行时间
     * @return
     */
    @Select("select COALESCE(sum(total_spent),0) as seconds from device_status_timeline where to_char(event_time,'yyyy-MM') = to_char(NOW(),'yyyy-MM') and status = '1'")
    Long getAllDeviceSumRunningMonth();


    /**
     * @desc 获取当月指定设备开机时间
     * @return
     */
    @Select("select COALESCE(sum(total_spent),0) as seconds from device_status_timeline where device_id=#{deviceId} and to_char(event_time,'yyyy-MM') = to_char(NOW(),'yyyy-MM') and status = '3'")
    Long getDeviceSumOpeningMonth(String deviceId);


    /**
     * @desc 获取当月指定设备运行时间
     * @return
     */
    @Select("select COALESCE(sum(total_spent),0) as seconds from device_status_timeline where device_id=#{deviceId} and to_char(event_time,'yyyy-MM') = to_char(NOW(),'yyyy-MM') and status = '1'")
    Long getDeviceSumRunningMonth(String deviceId);


    /**
     * @desc 获取所有设备的当月之前所有月份的设备运行时间
     * @return
     */
    @Select("SELECT * FROM (SELECT sum(month_running_time), to_char(create_time,'yyyy-MM') AS date  from device_month_reporter GROUP BY to_char(create_time,'yyyy-MM')) tt ORDER BY date")
    List<RunningTimeAllMonth> getAllDeviceSumRunningMonthBefore();



    /**
     * @desc 获取所有的设备当月之前所有月份的开机时间
     * @return
     */
    @Select("SELECT * FROM (SELECT sum(month_opening_time), to_char(create_time,'yyyy-MM') AS date  from device_month_reporter GROUP BY to_char(create_time,'yyyy-MM')) tt ORDER BY date")
    List<RunningTimeAllMonth> getAllDeviceSumOpeningMonthBefore();


    /**
     * @desc 获取所有的设备当月之前所有月份的开机和运行时间
     * @return
     */
    @Select("SELECT * FROM (SELECT COALESCE(sum(month_running_time),0) AS runningTimes, COALESCE(sum(month_opening_time),0) AS openTimes, to_char(create_time,'yyyy-MM') AS date  " +
            "from device_month_reporter GROUP BY to_char(create_time,'yyyy-MM')) tt ORDER BY date")
    List<RunAndOpenTimesAllMonth> getAllDeviceSumOpeningAndRunningMonthBefore();


    /**
     * @desc 获取所有设备的各个月份的时间稼动率 2021.12.03
     */
    @Select("select COALESCE(sum(time_grain_move_rate),0) as total, to_char(create_time, 'yyyy-MM') as date from " +
            "(select * from device_month_reporter where create_time > #{startTime}) tt group by to_char(create_time, 'yyyy-MM') order by date")
    List<RunningTimesEachMonth> getSumTimeGrainRateEachMonth(@Param("startTime") Date startTime);


    /**
     * @desc 获取所有设备的各个月份的性能稼动率 2021.12.03
     */
    @Select("select COALESCE(sum(performance_grain_move_rate),0) as total, to_char(create_time, 'yyyy-MM') as date from " +
            "(select * from device_month_reporter where create_time > #{startTime}) tt group by to_char(create_time, 'yyyy-MM') order by date")
    List<RunningTimesEachMonth> getSumPerformanceGrainRateEachMonth(@Param("startTime") Date startTime);
}
